import time

import aiomysql.cursors
import asyncio
import traceback

from logger_config import logger
from test.test_utils import TestUtils


class AioMysqlClient():
    def __init__(self, host, port, user, password, db_name, **kwargs):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db_name = db_name
        self.kwargs = kwargs  # 其他参数
        self.conn_pool = None  # 连接池
        self.is_connected = False  # 是否处于连接状态
        self.lock = asyncio.Lock()  # 异步锁

    async def init_pool(self):
        """
        创建数据库连接
        :return:
        """
        try:
            self.conn_pool = await aiomysql.create_pool(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                db=self.db_name,
                autocommit=True,
                connect_timeout=3,
                **self.kwargs
            )
            self.is_connected = True
            logger.info(f'aiomysql connect success')
        except:
            logger.error(traceback.format_exc())
            self.is_connected = False

        return self

    async def insert(self, sql, args=None):
        try:
            async with self.conn_pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cur:
                    await cur.execute(sql, args)
                    await conn.commit()
                    logger.info(f'Data inserted successfully')
        except:
            await conn.rollback()
            logger.error(traceback.format_exc())
            self.is_connected = False
            await self.re_conn()

    async def fetch_one(self, sql, args=None):
        try:
            async with self.conn_pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cur:
                    await cur.execute(sql, args)
                    res = await cur.fetchone()
                    if cur.rowcount == 0:
                        return None
                    return res
        except:
            logger.error(traceback.format_exc())
            self.is_connected = False
            await self.re_conn()

    async def fetch_all(self, sql, args=None):
        try:
            async with self.conn_pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cur:
                    await cur.execute(sql, args)
                    if cur.rowcount == 0:
                        return None
                    res = await cur.fetchall()
                    return res
        except:
            logger.error(traceback.format_exc())
            self.is_connected = False
            await self.re_conn()

    async def re_conn(self):
        logger.info('aiomysql reconnect')
        await self.init_pool()


async def connect_mysql():
    mysql_client = AioMysqlClient(**{
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'password': '123456',
        'db_name': 'DatabaseCDXX',
    })
    await mysql_client.init_pool()
    return mysql_client


async def test_join_select():
    mysql_client = await connect_mysql()
    print(mysql_client.is_connected)
    sql = ("insert into MES(data100,data101,data102,data103,data104,data105,data106,"
           "data107,data108,data109,data110,data111,data112,data113) values(%s, %s, %s, %s, %s, %s ,%s ,%s, %s, %s, %s ,%s ,%s ,%s)")
    await mysql_client.insert(sql, args=(1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 1))


if __name__ == "__main__":
    TestUtils.run_async(test_join_select())
